import pymysql
import pandas as pd
import pymysql.cursors


class MysqlUtils(object):
    """数据库工具类
    """
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            database='scenic',
            port=3306,  
            charset='utf8'  
        )
        
    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT order_id,id_no,user_name,phone,age,user_count FROM ( SELECT o.id as order_id,u.id_no ,u.user_name,u.phone,
        CASE
            WHEN length(u.id_no) =18 THEN YEAR(now()) - cast(substr(u.id_no,7,4) as signed)
            else NULL
        END as age,
        (SELECT count(*) FROM ticket_order_user_rel WHERE order_id = o.id) as user_count
        FROM ticket_order o JOIN ticket_order_user_rel u on u.order_id = o.id WHERE o.pay_time is not null and 
        o.pay_time != '' and u.id_no is not null and u.id_no !='' ) as subquery WHERE user_count = 1 and (age <18 or age>=60)
    
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df)
        df.to_csv('./outlier/scenic_data.csv')
        
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()